/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.SQL;

import Server.Helper.Analyzer;
import Server.Helper.StringHelper;
import java.util.Vector;
import Server.MyDataType.MyDataDynamic;
import Server.MyDataType.MyRowData;
import Server.MyDataType.MyTable;
import Server.lastproject.XMLManage;

/**
 *
 * @author Gnoc
 */

/*
 *  Lớp chứa đối tượng là 1 câu lệnh select với các field như dưới.
 * Cú pháp: SELECT colums FROM table WHERE codition
 */
public class SelectCommand extends MyCommand
{
    public SelectCommand(String command)
    {
        super(command);
        _xmlManage = new XMLManage();
    }
    
    public String[] colums; // DS các cột cần select.
    public String table;    // tên bảng cần select
    public String condition; // Điều kiện select (biểu thức sau where)
    
    /*
     * Tạo câu select từ string truyền vào.
     * Phân tách ra các field của câu select.
     */
    
    public int checkCommaOrSpace(String command)
    {
        int len = command.length();
        boolean bComma = false;
        boolean bSpace = false;
        
        for(int i = 0; i < len; i++)
        {
            if(command.charAt(i) == ' ' )
            {
                bSpace = true;
                break;
            }
            
            if(command.charAt(i) == ',')
            {
                bComma = true;
                break;
            }
        }
        if(bSpace)
            return 1;
        return 0;
    }
    
    public MyDataDynamic Analysic_SelectCommand(String command)
    {
        // Chuẩn hóa
        command = new Analyzer().MakeCommandStandardization4InsertSelect(command);
        String[] lstFirt = command.split(" ");
        if(lstFirt[0].toUpperCase().compareTo("SELECT") == 0)
        {
            command= "";
            for(int i = 1; i<lstFirt.length; i++)
                command += lstFirt[i] + " ";
            command = command.trim();
        }
        
       MyDataDynamic myData = new MyDataDynamic();
       Vector<MyTable> vtMyTable = new Vector<MyTable>(0);
       Vector<String[]> vtLstConditionals = new Vector<String[]>(0); 
       Vector<String> vtSelectionCollumns = new Vector<String>();
       
       boolean bSelectSpecifies = true;
       try
       {
           command = command.trim();           
           if(command.indexOf("*") >= 0)
           {
               bSelectSpecifies = false;               
               _sCommand = command; //TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST
               splitFirstString(_sCommand, " ");
           }
           else
           {
               _sCommand = command; //TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST
               int index_From = _sCommand.indexOf("from");
               String sStringColumns = _sCommand.substring(0, index_From);
               String[] sSplitColumns = sStringColumns.split(",");
               for(int i = 0; i < sSplitColumns.length; i++)
               {
                   vtSelectionCollumns.add(sSplitColumns[i]);
               }
               
               _sCommand = _sCommand.substring(index_From, _sCommand.length());
               
           }
          
           //nếu chọn hết dữ liệu trong bảng ra luôn
           _sCommand = _sCommand.trim();
            String sFrom = splitFirstString(_sCommand, " ");
            if(sFrom.equals("from"))
            {
                /*
                * 1 : dau cach
                * 0 : dau phay
                */
                _sCommand = _sCommand.trim();
                int bCheckCommaOrSpace = 1;
                bCheckCommaOrSpace = checkCommaOrSpace(_sCommand);

                //String table = splitFirstString(_sCommand, " ");
                if(_sCommand.indexOf(",") > 0)
                {
                    //vị trí của phần từ where
                    int index_Where = _sCommand.indexOf("where");
                    if(index_Where == -1)
                    {
                        String[] lstNameTable = _sCommand.split(",");
                        for(int i = 0; i < lstNameTable.length; i++)
                        {
                            MyTable myTable = new MyTable();
                            myTable._sTableName = lstNameTable[i];
                            myTable._sTableName = myTable._sTableName.trim();
                            vtMyTable.add(myTable);
                        }
                        _sCommand = "";
                    }
                    else
                    {                           
                        String stringTable = _sCommand.substring(0, index_Where);
                        String[] lstNameTable = stringTable.split(",");
                        for(int i = 0; i < lstNameTable.length; i++)
                        {
                            MyTable myTable = new MyTable();
                            myTable._sTableName = lstNameTable[i];
                            myTable._sTableName = myTable._sTableName.trim();
                            vtMyTable.add(myTable);
                        }
                        _sCommand = _sCommand.substring(index_Where, _sCommand.length());
                        splitFirstString(_sCommand, " ");
                    }
                    // lstNameTable = new String[lstNameTable.length];
                    int len = vtMyTable.size();
                    for(int i = 0; i < len; i++)
                    {
                        MyTable myTable = vtMyTable.elementAt(i);

                        if(myTable._sTableName.indexOf(" ") > 0)
                        {
                            String[] lstSplit = myTable._sTableName.split(" ");
                            myTable._sIndentifyNameTable =  lstSplit[1];
                            myTable._sTableName = lstSplit[0];
                        }
                    }
                }
                else
                {
                    // case 1 : select * from Table_A tba
                    // case 2: select * from Table_A tba where...
                    MyTable myTable = new MyTable();
                    
                    _sCommand = _sCommand.trim();
                    int index_space = _sCommand.indexOf(" ");
                    if(index_space != -1)
                    {
                        String nameTable = splitFirstString(_sCommand, " ");
                        myTable._sTableName = nameTable;
                    }
                    else
                    {
                        if(_sCommand.length() != 0)
                        {
                            myTable._sTableName = _sCommand;
                            _sCommand = "";
                        }
                    }
                    
                    
                    // case 1 : tba 
                    // case 2: tba where....
                    _sCommand = _sCommand.trim();                    
                    String[] sTestWhere = _sCommand.split(" ");
                    if( !(sTestWhere != null && sTestWhere.length > 0 && sTestWhere[0].toLowerCase().equals("where")))
                    {                                            
                        index_space = _sCommand.indexOf(" ");
                        if(index_space != -1)
                        {
                            String sNameIndentifyTable = splitFirstString(_sCommand," ");
                            myTable._sIndentifyNameTable = sNameIndentifyTable;
                        }
                        else
                        {
                            if(_sCommand.length() != 0)
                            {
                                myTable._sIndentifyNameTable = _sCommand;
                                _sCommand = "";
                            }
                        }
                    }
                    vtMyTable.add(myTable);                       
                }   

                boolean bExistTable = true;
                int Amount_Table = vtMyTable.size();
                for(int i = 0; i < Amount_Table; i++ )
                {                                             
                    bExistTable |= _xmlManage.checkExistTable(vtMyTable.elementAt(i)._sTableName);
                }

                if(!bExistTable)
                    return myData;

                _sCommand = _sCommand.trim();
                //bổ sung thông tin tên cột ứng với từng bảng        
                for(int j = 0; j < Amount_Table; j++)
                {
                    MyTable table = vtMyTable.elementAt(j);
                    //lấy danh sách tên cột
                    String[] listNameColumn = _xmlManage.getAllNameColumn(table._sTableName);

                    for(int t = 0; t < listNameColumn.length; t++)
                    {
                        table._vtNameColumns.add(listNameColumn[t]);
                    }                        
                } 

                //nếu có thêm câu điều kiện where....
                if(!_sCommand.equals(""))
                {
                    String sWhere = null;
                    if(_sCommand.indexOf("where") >= 0 || _sCommand.indexOf("WHERE") >= 0)
                    {
                        sWhere = splitFirstString(_sCommand, " ");
                    }

                    if(_sCommand.length() == 0)
                        return myData;

                    int iCount_And = 0;
                    int index_And = -1;
                    do
                    {
                        index_And = _sCommand.indexOf( "and", index_And + 1);
                        if(index_And == -1)
                        {
                            index_And = _sCommand.indexOf( "AND", index_And + 1);
                        }
                        if(index_And != -1)
                        {
                            iCount_And++;                               
                        }
                    }
                    while(index_And != -1);

                    for(int i = 0; i < iCount_And + 1; i++)
                    {                          
                        
                        String fLeftConditional = splitFirstString(_sCommand, " ");
                        _sCommand.trim();
                        String sOperand = splitFirstString( _sCommand,  " ");

                        _sCommand.trim();                            
                        String fRightConditional = "";
                        int[] index = getIndexCouple_Quote(_sCommand);
                       // if((index[0] == -1 && index[1] != -1) || (index[1] == -1 && index[0] != -1) )
                         //   return myData;
                        
                        if(index[0] != -1 && index[1] != -1)
                        {
                            fRightConditional = _sCommand.substring(index[0] + 1, index[1]);
                            _sCommand = _sCommand.substring(index[1] + 1, _sCommand.length());
                        }
                        else                                
                        {
                            fRightConditional = _sCommand.trim();
                        }

                        String[] conditional = new String[3];
                        if(!(fLeftConditional == null && sOperand == null && fRightConditional == null))
                        {
                            conditional[0] = fLeftConditional;
                            conditional[1] = sOperand;
                            conditional[2] = fRightConditional;
                            vtLstConditionals.add(conditional);          
                        }
                        _sCommand = _sCommand.trim();
                        String and = splitFirstString(_sCommand,  " ");
                    }

                    //nếu không có  từ khóa 'and'   <-> select * from HOC_SINH where HOC_SINH.Ho_Ten = 'Nguyen Duc Cang'                      
                    if(iCount_And  == 0)
                    {
                        String fLeftConditional = splitFirstString(_sCommand, " ");
                        _sCommand.trim();
                        String sOperand = splitFirstString( _sCommand,  " ");

                        _sCommand.trim();
                        String fRightConditional = "";
                        int[] index = getIndexCouple_Quote(_sCommand);
                        if((index[0] == -1 && index[1] != -1) || (index[1] == -1 && index[0] != -1) )
                            return myData;

                        if(index[0] != -1 && index[1] != -1)
                        {
                            fRightConditional = _sCommand.substring(index[0] + 1, index[1]);
                            _sCommand = _sCommand.substring(index[1], _sCommand.length());
                        }
                        else                                
                        {
                            fRightConditional = splitFirstString(_sCommand,  " ");
                        }

                        if(!(fLeftConditional == null && sOperand == null))
                        {
                            String[] conditional = new String[3];
                            conditional[0] = fLeftConditional;
                            conditional[1] = sOperand;
                            conditional[2] = fRightConditional;
                            vtLstConditionals.add(conditional);   
                        }
                    }

                    //merge  du lieu khi chua con conditional
                    MyDataDynamic myDataDynamicTable_Primitive = new MyDataDynamic();
                    int AmountTable = vtMyTable.size();
                    MyDataDynamic myDataDynamic_Table2;
                    for(int i = 0; i < AmountTable; i++)
                    {
                        myDataDynamic_Table2 = _xmlManage.getMyDataDynamic( vtMyTable.elementAt(i)._sTableName );
                        myDataDynamicTable_Primitive =  _xmlManage.mergeTable(myDataDynamicTable_Primitive, "Database", myDataDynamic_Table2, vtMyTable.elementAt(i)._sTableName );
                    }
                    MyDataDynamic myDataDynamicProcessWhere =  Process_Where(myDataDynamicTable_Primitive, vtLstConditionals, vtMyTable);
                    myData = myDataDynamicProcessWhere;
                }
                else
                {
                    Vector<MyRowData> myFullRowData = new Vector<MyRowData>();
                    Vector<String> vtNameTable = new Vector<String>();
                    Vector<String> vtNameColumnCurrent = new Vector<String>(0);
                    int iAmountTable = vtMyTable.size();
                    for(int i = 0; i < iAmountTable; i++)
                    {
                        MyTable myTable1 = vtMyTable.elementAt(i);
                        Vector<MyRowData> vtData1 = _xmlManage.getAllRowData(myTable1._sTableName);
                        for(int j = i + 1; j< iAmountTable; j++)
                        {
                            MyTable myTable2 = vtMyTable.elementAt(j);                            
                            Vector<MyRowData> vtData2 = _xmlManage.getAllRowData(myTable2._sTableName);
                            Vector<MyRowData> vtDataMerge = mergeRowData(vtData1, vtData2);
                            myFullRowData.addAll( vtDataMerge );

                        }
                        
                        if(iAmountTable == 1)
                        {
                            myFullRowData = vtData1;
                        }
                        String[] lstTempColumn = _xmlManage.getAllNameColumn(myTable1._sTableName);
                        for(int c = 0; c < lstTempColumn.length; c++)
                        {
                                vtNameColumnCurrent.add( myTable1._sTableName + "." + lstTempColumn[c]);
                        }
                        vtNameTable.add(myTable1._sTableName);
                    }                       
                    myData._vtRowData = myFullRowData;
                    myData._vtNameColumn = vtNameColumnCurrent;          
                }
            }
            
            //nếu chọn những cột chỉ rõ
            if(bSelectSpecifies)
            {                
                Vector vtIndexSelection = new Vector();
               int iAmountSelectionColumn = vtSelectionCollumns.size();
               int iAmountTable = vtMyTable.size();
               
               //lấy danh sách tên bảng
               String[] sNameTables = new String[iAmountTable];
               for(int j = 0; j < sNameTables.length; j++)
               {
                   sNameTables[j] = vtMyTable.elementAt(j)._sTableName;
               }
               
               for(int i = 0; i < iAmountSelectionColumn; i++)
               {
                   String nameColumnSelection = vtSelectionCollumns.elementAt(i);
                   nameColumnSelection = nameColumnSelection.trim();
                   String fullNameColumnSelection = nameColumnSelection;
                   String[] sSplitInfoColumn = null;
                   if(nameColumnSelection.indexOf(".") > 0)
                   {
                     sSplitInfoColumn = nameColumnSelection.split("\\.");
                   }
                   
                    for(int j = 0; j < iAmountTable; j++)
                    {
                        if( sSplitInfoColumn != null && sSplitInfoColumn[0].equals( vtMyTable.elementAt(j)._sIndentifyNameTable ))
                        {
                            fullNameColumnSelection = vtMyTable.elementAt(j)._sTableName + "." + sSplitInfoColumn[1];
                            break;
                        }                          
                    }
                   
                    if(sSplitInfoColumn == null)
                    {
                        nameColumnSelection = nameColumnSelection.trim();
                        String test = _xmlManage.checkExistColumn(sNameTables, nameColumnSelection);
                        fullNameColumnSelection = test + "." + nameColumnSelection.toUpperCase();
                    }
                    
                    //kiểm tra tên cột này trong bảng kết quả trả về, nếu tồn tại trả về index của tên cột dó
                    int iAmountColumnFull = myData._vtNameColumn.size();
                    for(int t = 0;t < iAmountColumnFull; t++)
                    {
                        if(myData._vtNameColumn.elementAt(t).equals(fullNameColumnSelection))
                        {
                            vtIndexSelection.add(t);
                        }
                    }                    
               }
               
               //xóa những cột không thỏa đi
               int iAmountColumnFull = myData._vtNameColumn.size();
               int iAmountDelete = 0;
               for(int t = 0; t < iAmountColumnFull; t++)
               {
                   if(!vtIndexSelection.contains(t))
                   {
                       myData._vtNameColumn.remove(t - iAmountDelete);
                       
                       int iAmountRowData = myData._vtRowData.size();
                       for(int j = 0; j < iAmountRowData; j++)
                       {
                            myData._vtRowData.elementAt(j)._vtRow.remove(t - iAmountDelete);
                       }
                       
                       iAmountDelete++;
                   }
               }                              
               
            }
           
       }
       catch(Exception ex)
       {
           int a = 4;
           a--;
       }
       return myData;
       
    }
    
    public MyDataDynamic Process_Where(MyDataDynamic myDataPrimitive, Vector<String[]> vtLstConditionals, Vector<MyTable> vtMyTable)
    {
        int iAmountTable = vtMyTable.size();
        String[] lstNameTable = new String[iAmountTable];
        for(int i = 0; i < iAmountTable; i++)
        {
            lstNameTable[i] = vtMyTable.elementAt(i)._sTableName;
        }
        
        int Amount_Conditional = vtLstConditionals.size();
        
        for(int i = 0; i < Amount_Conditional; i++)
        {
            String[] conditional = vtLstConditionals.elementAt(i);
            String NameColumn_Table1 = null;
            String NameColumn_Table2 = null;
            
            String NameTable1 = null;
            String NameTable2 = null;
            
            String value = null;            
            
            if(conditional[0].indexOf(".") > 0)
            {
                String[] sSplit = conditional[0].split("\\.");                               
                NameColumn_Table1 = sSplit[1];
                NameTable1 = sSplit[0];
            } 
            else
            {
                NameColumn_Table1 = conditional[0];
                NameTable1 = _xmlManage.checkExistColumn(lstNameTable, NameColumn_Table1);
            }
            
            if(conditional[2].indexOf(".") > 0)
            {
                String[] sSplit = conditional[2].split("\\.");                               
                NameColumn_Table2 = sSplit[1];
                NameTable2 = sSplit[0];
            }
            else
            {
                NameTable2 = _xmlManage.checkExistColumn(lstNameTable, conditional[2]);
                if(NameTable2 == null)
                {
                    value = conditional[2];
                }
            }
            
            //kiem tra su ton tai cua table
            boolean bExistTable1 = false;
            boolean bExistTable2 = false;
            
            for(int j = 0; j < iAmountTable; j++)
            {
                MyTable myTable1 = (MyTable)vtMyTable.elementAt(j);
                MyTable myTable2 = (MyTable)vtMyTable.elementAt(j);
                
                if(myTable1._sIndentifyNameTable != null && myTable1._sIndentifyNameTable.equals(NameTable1))
                {
                    NameTable1 = myTable1._sTableName;
                    bExistTable1 = true;
                }
                
                bExistTable1 |= (myTable1)._sTableName.equals(NameTable1);                
                if(NameTable2 != null)
                {
                    if(myTable2._sIndentifyNameTable != null && myTable2._sIndentifyNameTable.equals(NameTable2))
                    {
                        NameTable2 = myTable2._sTableName;
                        bExistTable2 = true;
                    }                    
                    bExistTable2 |= ((MyTable)vtMyTable.elementAt(j))._sTableName.equals(NameTable2);                
                }
                
                //if((bExistTable1 && NameColumn_Table2 == null) || (bExistTable1 && bExistTable2))
                    //break;
                
            }
            
           //if(!bExistTable1 || (NameColumn_Table2 != null && bExistTable2))
               // return myDataPrimitive;
            
            String fullNameColumn_Table1 = NameTable1 + "." + NameColumn_Table1.toUpperCase();
            
            String fullNameColumn_Table2 = null;
            if(NameColumn_Table2 != null && NameTable2 != null)
                fullNameColumn_Table2 = NameTable2 + "." + NameColumn_Table2.toUpperCase();
            
            myDataPrimitive = _xmlManage.filterDataDynamicWithConditional(myDataPrimitive, conditional[1], fullNameColumn_Table1, fullNameColumn_Table2, value);
        }
        
        return myDataPrimitive;
    }
    
    private Vector<MyRowData>  mergeRowData(Vector<MyRowData> rowData1, Vector<MyRowData> rowData2)
    {
        Vector<MyRowData> vtResult = new Vector<MyRowData>(0);
        int iCount1 = rowData1.size();
        int iCount2 = rowData2.size();
        for(int i = 0; i < iCount1; i++)
        {
            MyRowData row1 = rowData1.elementAt(i);                                    
            for(int j = 0; j < iCount2; j++)
            {
                MyRowData row = new MyRowData();
                row._vtRow.addAll(row1._vtRow);
                row._vtRow.addAll(rowData2.elementAt(j)._vtRow);
                vtResult.add(row);
            }

        }
        
        return vtResult;
    }
}
